package app.models;

import app.database.MainDatabase;
import framework.spatial.*;
import java.awt.Point;
import java.awt.image.BufferedImage;
import java.io.IOException;
import java.net.URL;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import javax.imageio.ImageIO;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.spatial.geometry.JGeometry;
import oracle.sql.STRUCT;

/**
 *
 * @author Lukáš Marek
 */
public class SpatialModel implements FRDSpatialModel
{
	private MainDatabase db;
	
	public SpatialModel(MainDatabase db)
	{
		this.db = db;
	}

	@Override
	public LinkedList getData(LinkedList<String> params)
	{
		LinkedList<FRDSpatialGItem> list = new LinkedList<>();
		String sql = "SELECT * FROM map_objects";
		OracleResultSet rset;
		OraclePreparedStatement stmt;
		try
		{
			stmt = (OraclePreparedStatement) db.conn.prepareStatement(sql);
			rset = (OracleResultSet) stmt.executeQuery();
			while (rset.next())
			{
				STRUCT struct_data = (oracle.sql.STRUCT) rset.getObject("geometry");		//precteni geometrie z databaze
				JGeometry j_geom = JGeometry.load(struct_data);													//nacteni STRUCT do JGeometry

				double [] point = j_geom.getPoint();																		//souradnice bodu
				int [] elem = j_geom.getElemInfo();																			//pole Elem info dat
				double [] data = j_geom.getOrdinatesArray();														//pole bodu

				Point _p1 = new Point();
				Point _p2 = new Point();
				if (j_geom.getType() == JGeometry.GTYPE_POINT) //bod
				{
					FRDSpatialGPoint dbPoint = new FRDSpatialGPoint();
					_p1.setLocation((int)point[0], (int)point[1]);
					dbPoint.setLocation(_p1);
					dbPoint.setId(rset.getInt("object_id"));
					BufferedImage image = null;
					try
					{
						URL resource = getClass().getResource("/data/symbol_cave.png");
						if (resource != null) image = ImageIO.read(resource);
					}
					catch (IOException ex) { System.err.println(ex.getMessage()); }
					dbPoint.setImage(image);
					list.add(dbPoint);
				}
				else if(elem[1] == 1003) switch(elem[2])
				{
					case 3://ctverec
						FRDSpatialGRectangle rect = new FRDSpatialGRectangle();
						_p1.setLocation((int)data[0], (int)data[1]);
						_p2.setLocation((int)data[2], (int)data[3]);
						rect.setLocation(_p1,_p2);
						rect.setId(rset.getInt("object_id"));
						list.add(rect);
						break;
					case 4://kruznice
						FRDSpatialGCircle circle = new FRDSpatialGCircle();
						_p1.setLocation((int)data[0], (int)data[1]);
						_p2.setLocation((int)data[4], (int)data[5]);
						int _size = (_p2.y - _p1.y)/2;
						_p1.y += _size;
						circle.setLocation(_p1,_p2);
						circle.setId(rset.getInt("object_id"));
						list.add(circle);
						break;
					case 1://polygon
						LinkedList<Point> _points = new LinkedList<>();
						FRDSpatialGPolygon polygon = new FRDSpatialGPolygon();
						int i = 0;
						while (i < data.length)
						{
							_points.add(new Point((int)data[i], (int)data[i+1]));
							i += 2;
						}
						polygon.setId(rset.getInt("object_id"));
						polygon.setLocation(_points);
						list.add(polygon);
						break;
				}
				else if (elem[1] == 2) //cara
				{
					LinkedList<Point> _points = new LinkedList<>();
					FRDSpatialGLine line = new FRDSpatialGLine();
					int i = 0;
					while (i < data.length)
					{
						_points.add(new Point((int)data[i], (int)data[i+1]));
						i += 2;
					}
					line.setId(rset.getInt("object_id"));
					line.setLocation(_points);
					list.add(line);
				}
			}
			rset.close();
			stmt.close();
		}
		catch (SQLException ex)	{ System.err.println(ex.getMessage()); }
		return list;
	}

	@Override
	public int getDataCount(LinkedList<String> params)
	{
		int count = 0;
		String sql = "SELECT COUNT(*) FROM map_objects";
		OracleResultSet rset;
		OraclePreparedStatement stmt;
		try
		{
			stmt = (OraclePreparedStatement) db.conn.prepareStatement(sql);
			rset = (OracleResultSet) stmt.executeQuery();
			rset.next();
			count = rset.getInt(1);
			rset.close();
			stmt.close();
		}
		catch (SQLException ex){ System.err.println(ex.getMessage()); }
		return count;
	}

	@Override
	public void addItem(FRDSpatialGItem item)
	{
		String sql = "";
		if (item instanceof FRDSpatialGPoint)
		{
			Point p = ((FRDSpatialGPoint)item).getLocation();
			sql = "INSERT INTO map_objects (name,geometry) VALUES ('', "
					+ "SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE("+ p.x +","+ p.y +", NULL), NULL, NULL))";		
		}
		else if (item instanceof FRDSpatialGRectangle)
		{
			Point[] points = ((FRDSpatialGRectangle)item).getLocation();
			Point p1 = points[0];
			Point p2 = points[1];
			sql = "INSERT INTO map_objects (name,geometry) VALUES ('', "
					+ "SDO_GEOMETRY(2003, NULL,NULL, "
					+ "SDO_ELEM_INFO_ARRAY(1, 1003, 3),"
					+ "SDO_ORDINATE_ARRAY("+ p1.x +","+ p1.y +", "+ p2.x +","+ p2.y +")))";
		}
		else if (item instanceof FRDSpatialGCircle)
		{
			Point[] points = ((FRDSpatialGCircle)item).getLocation();
			Point p = points[0];
			int size = (int) p.distance(points[1]);
			Point p1 = new Point(p.x, p.y - size);
			Point p2 = new Point(p.x + size, p.y);
			Point p3 = new Point(p.x, p.y + size);
			sql = "INSERT INTO map_objects (name,geometry) VALUES ('', "
					+ "SDO_GEOMETRY(2003, NULL,NULL, "
					+ "SDO_ELEM_INFO_ARRAY(1, 1003, 4),"
					+ "SDO_ORDINATE_ARRAY("+ p1.x +","+ p1.y +", "+ p2.x +","+ p2.y +", "+ p3.x +","+ p3.y +")))";
		}
		else if (item instanceof FRDSpatialGLine)
		{
			LinkedList<Point> points = ((FRDSpatialGLine)item).getLocation();
			String point_string = "";
			point_string += points.get(0).x +","+ points.get(0).y;
			for (int i=1; i < points.size(); i++)
			{
				point_string += ", "+ points.get(i).x +","+ points.get(i).y;
			}
			sql = "INSERT INTO map_objects (name,geometry) VALUES ('', "
					+ "SDO_GEOMETRY(2002, NULL,NULL, "
					+ "SDO_ELEM_INFO_ARRAY(1, 2, 1),"
					+ "SDO_ORDINATE_ARRAY("+ point_string +")))";
		}
		else if (item instanceof FRDSpatialGPolygon)
		{
			LinkedList<Point> points = ((FRDSpatialGPolygon)item).getLocation();
			String point_string = "";
			point_string += points.get(0).x +","+ points.get(0).y;
			for (int i=1; i < points.size(); i++)
			{
				point_string += ", "+ points.get(i).x +","+ points.get(i).y;
			}
			sql = "INSERT INTO map_objects (name,geometry) VALUES ('', "
					+ "SDO_GEOMETRY(2003, NULL,NULL, "
					+ "SDO_ELEM_INFO_ARRAY(1, 1003, 1),"
					+ "SDO_ORDINATE_ARRAY("+ point_string +")))";
		}
		
		if (!"".equals(sql))
		try
		{
			db.executeUpdate(sql);			//vlozeni prvku do databaze
			ResultSet rset;
			rset = db.executeQuery("SELECT MapObj_ObjectID_s.currval FROM dual");
			if(rset!=null)
			{
				rset.next();
				item.setId(rset.getInt(1));							//ulozeni ID z databaze
				rset.close();
			}
			else
				System.err.println("Chyba: pří vkládání objektu do databáze došlo k chybě");
		}
		catch (SQLException ex) { System.err.println(ex.getMessage()); }			
	}

	@Override
	public void editItem(FRDSpatialGItem item)
	{
		int id = item.getId();
		String sql = "";
		if (item instanceof FRDSpatialGPoint)
		{
			Point p = ((FRDSpatialGPoint)item).getLocation();
			sql = "UPDATE map_objects SET geometry="
					+ "SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE("+ p.x +","+ p.y +", NULL), NULL, NULL) " 
					+ "WHERE object_id = " + id;
		}
		else if (item instanceof FRDSpatialGRectangle)
		{
			Point[] points = ((FRDSpatialGRectangle)item).getLocation();
			Point p1 = points[0];
			Point p2 = points[1];
			sql = "UPDATE map_objects SET geometry="
					+ "SDO_GEOMETRY(2003, NULL,NULL, "
					+ "SDO_ELEM_INFO_ARRAY(1, 1003, 3),"
					+ "SDO_ORDINATE_ARRAY("+ p1.x +","+ p1.y +", "+ p2.x +","+ p2.y +"))"
					+ "WHERE object_id = " + id;
		}
		else if (item instanceof FRDSpatialGCircle)
		{
			Point[] points = ((FRDSpatialGCircle)item).getLocation();
			Point p = points[0];
			int size = (int) p.distance(points[1]);
			Point p1 = new Point(p.x, p.y - size);
			Point p2 = new Point(p.x + size, p.y);
			Point p3 = new Point(p.x, p.y + size);
			
			sql = "UPDATE map_objects SET geometry="
					+ "SDO_GEOMETRY(2003, NULL,NULL, "
					+ "SDO_ELEM_INFO_ARRAY(1, 1003, 4),"
					+ "SDO_ORDINATE_ARRAY("+ p1.x +","+ p1.y +", "+ p2.x +","+ p2.y +", "+ p3.x +","+ p3.y +"))"
					+ "WHERE object_id = " + id;
		}
		else if (item instanceof FRDSpatialGLine)
		{
			LinkedList<Point> points = ((FRDSpatialGLine)item).getLocation();
			String point_string = "";
			point_string += points.get(0).x +","+ points.get(0).y;			
			for (int i=1; i < points.size(); i++)
			{
				point_string += ", "+ points.get(i).x +","+ points.get(i).y;
			}
			sql = "UPDATE map_objects SET geometry="
					+ "SDO_GEOMETRY(2002, NULL,NULL, "
					+ "SDO_ELEM_INFO_ARRAY(1, 2, 1),"
					+ "SDO_ORDINATE_ARRAY("+ point_string +"))"
					+ "WHERE object_id = " + id;
		}
		else if (item instanceof FRDSpatialGPolygon)
		{
			LinkedList<Point> points = ((FRDSpatialGPolygon)item).getLocation();
			String point_string = "";
			point_string += points.get(0).x +","+ points.get(0).y;
			for (int i=1; i < points.size(); i++)
			{
				point_string += ", "+ points.get(i).x +","+ points.get(i).y;
			}
			sql = "UPDATE map_objects SET geometry="
					+ "SDO_GEOMETRY(2003, NULL,NULL, "
					+ "SDO_ELEM_INFO_ARRAY(1, 1003, 1),"
					+ "SDO_ORDINATE_ARRAY("+ point_string +"))"
					+ "WHERE object_id = " + id;
		}
		
		if (!"".equals(sql))
		try
		{
			db.executeUpdate(sql);
		}
		catch (SQLException ex) { System.err.println(ex.getMessage()); }
	}

	@Override
	public void deleteItem(FRDSpatialGItem item)
	{
		int id = item.getId();
		String sql = "DELETE FROM map_objects WHERE object_id = " + id;
		try
		{
			db.executeUpdate(sql);
		}
		catch (SQLException ex) { System.err.println(ex.getMessage()); }	
	}
	
	/**
	 * nacteni dodatecnych informaci o objektu
	 * @param id
	 * @return 
	 */
	public LinkedList<String> select(int id)
	{
		LinkedList<String> params = new LinkedList<>();
		String sql = "SELECT name, type, date_from, date_until FROM map_objects WHERE object_id = " + id;
		ResultSet rset;
		try
		{
			rset = db.executeQuery(sql);
			rset.next();
			params.add(rset.getString("name"));
			params.add(rset.getString("type"));
			params.add(rset.getString("date_from"));
			params.add(rset.getString("date_until"));
			rset.close();
		}
		catch (SQLException ex) { System.err.println(ex.getMessage()); }
		return params;
	}
	
	/**
	 * editace dodatecnych informaci o objektu
	 * @param id
	 * @param params 
	 */
	public void edit(int id, LinkedList<String> params)
	{
		String sql = "UPDATE map_objects SET name = ?, type = ?, date_from = ?, date_until = ?  WHERE object_id = ?";
		String name = params.get(0);
		String type = params.get(1);
		String date_from = params.get(2);
		String date_until = params.get(3);
		try
		{
			PreparedStatement stm = db.conn.prepareStatement(sql);
			stm.setString(1, name);
			stm.setString(2, type);
			stm.setString(3, date_from);
			stm.setString(4, date_until);
			stm.setInt(5, id);
			stm.executeUpdate();
		}
		catch (SQLException ex) { System.err.println(ex.getMessage()); }	
	}

	/**
	 * nasteni typu z multimedialni databaze
	 * @return 
	 */
	public LinkedHashMap<Integer,String> getTypes()
	{
		LinkedHashMap<Integer,String> list = new LinkedHashMap<>();
		list.put(-1, "");
		String sql = "SELECT id,name FROM monster_tab WHERE picture IS NOT NULL";
		OracleResultSet rset;
		OraclePreparedStatement stmt;
		try
		{
			stmt = (OraclePreparedStatement) db.conn.prepareStatement(sql);
			rset = (OracleResultSet) stmt.executeQuery();
			while (rset.next())
			{
				list.put(rset.getInt("id"), rset.getString("name"));
			}
			rset.close();
			stmt.close();
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
		}
		return list;
	}
}
